package com.srv.persistence.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.srv.Config;
import com.srv.common.DataSourceManager;
import com.srv.common.UserField;
import com.srv.model.User;
import com.srv.persistence.IUserPersistence;
import com.srv.persistence.PersistenceException;
import com.srv.util.DataBaseUtil;
import com.srv.util.ObjectField;
import com.srv.util.SqlUtil;
import com.srv.util.StringUtil;

public class DBUserPersistence implements IUserPersistence {
	private static Log log = LogFactory.getLog(DBUserPersistence.class);
	
	private static final String KEY_TABLE_NAME_PREFIX = "USER_DETAIL_";
	
	private static final int TABLE_NUM = Config.TABLE_NUM;
	
	public User getUser(long pid)throws PersistenceException{
		Map<ObjectField, Object> keyValues = new HashMap<ObjectField, Object>();
		keyValues.put(UserField.PID, pid);
		
		List<User> list = queryUserList(pid, keyValues);
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}

	public List<User> queryUserList(long pid, Map<ObjectField, Object> keyValue)throws PersistenceException {
		String sql = getSelectSql(pid, keyValue);
		log.debug("The User query sql is:" + sql) ;
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List<User> list = new ArrayList<User>();
		try {
			conn = DataSourceManager.get().getDataSource(pid).getConnection();
			st = conn.prepareStatement(sql);
			SqlUtil.setStmtParams(st, 1, keyValue);
			rs = st.executeQuery();
			
			while(rs.next()){
				list.add(rsToObject(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeResultSet(rs);
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
		return list;
	}
	
	public User insertUser(User user) throws PersistenceException {
		if(user.pid < 1){
			throw new PersistenceException("the column \"pid\" is must NOT NULL when insert user");
		}
		Date currentTime = new Date();
		if(user.createTime == null){
			user.createTime = currentTime;
		}
		if(user.updateTime == null){
			user.updateTime = currentTime;
		}
		if(user.lastLoginTime == null){
			user.lastLoginTime = currentTime;
		}
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try {
			conn = DataSourceManager.get().getDataSource(user.pid).getConnection();
			st = conn.prepareStatement(getInsertSql(user.pid));
			
			st.setLong(1, user.pid);
			st.setInt(2, user.sex);
			st.setTimestamp(3, new Timestamp(user.createTime.getTime()));
			st.setTimestamp(4, user.birthday == null ? null : new Timestamp(user.birthday.getTime()));
			st.setString(5, user.city);
			st.setTimestamp(6, new Timestamp(user.lastLoginTime.getTime()));
			st.setInt(7, user.isDel() ? 1 : 0);
			st.setTimestamp(8, new Timestamp(user.updateTime.getTime()));
			int flag = st.executeUpdate();
			if(flag <= 0){
				throw new PersistenceException("insert User fail");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.error("insert SummaryUser fail");
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
		return user;
	}

	public boolean updateUser(long pid, Map<ObjectField, Object> keyValue)
			throws PersistenceException {
		String sql = getUpdateSql(pid, keyValue);
		log.debug("The User update sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try {
			conn = DataSourceManager.get().getDataSource(pid).getConnection();
			st = conn.prepareStatement(sql);
			int idx = SqlUtil.setStmtParams(st, 1, keyValue);
			st.setLong(idx, pid);
			
			return st.executeUpdate() > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
	}
	
	private String getInsertSql(long pid){
		StringBuilder builder = new StringBuilder();
		
		builder.append("INSERT INTO ").append(KEY_TABLE_NAME_PREFIX);
		builder.append(StringUtil.appendZore(pid % TABLE_NUM, 2));
		builder.append(" (PID, SEX, CREATE_TIME, BIRTHDAY, CITY, LASTLOGIN_TIME, ISDEL, UPDATE_TIME)");
		builder.append(" VALUES ");
		builder.append("(?,?,?,?,?,?,?,?)");
		
		return builder.toString();
	}
	
	private String getUpdateSql(long pid, Map<ObjectField, Object> keyValues){
		StringBuilder strBuf = new StringBuilder();
		strBuf.append("UPDATE ");
		strBuf.append(KEY_TABLE_NAME_PREFIX + StringUtil.appendZore(pid % TABLE_NUM, 2));
		
		if (keyValues != null && keyValues.size() > 0) {
			strBuf.append(" SET ").append(SqlUtil.generateSetCause(keyValues));
		}
		strBuf.append(" WHERE PID = ?");
		
		return strBuf.toString();
	}
	private String getSelectSql(long pid, Map<ObjectField, Object> keyValues){
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT * FROM ");
		builder.append(KEY_TABLE_NAME_PREFIX + StringUtil.appendZore(pid % TABLE_NUM, 2));
		
		if (keyValues != null && keyValues.size() > 0) {
			builder.append(" WHERE ").append(SqlUtil.generateWhereCause(keyValues));
        }
		
        return builder.toString();
	}
	
	private User rsToObject(ResultSet rs) throws SQLException{
		User user = new User();
		
		user.pid = rs.getLong("PID");
		user.sex = rs.getInt("SEX");
		user.createTime = new Date(rs.getTimestamp("CREATE_TIME").getTime());
		Timestamp tmpTime = rs.getTimestamp("BIRTHDAY");
		user.birthday = (tmpTime == null ? null : new Date(tmpTime.getTime()));
		user.city = rs.getString("CITY");
		user.lastLoginTime = new Date(rs.getTimestamp("LASTLOGIN_TIME").getTime());
		user.setIsDel(rs.getInt("ISDEL"));
		user.updateTime = new Date(rs.getTimestamp("UPDATE_TIME").getTime());
		
		return user;
	}
}
